import pymysql
host='127.0.0.1'
port=3306
user='root'
password='wjc123456'
database='market'
charset='utf8'

def get_conn():
    db=pymysql.Connect(host=host,port=port,user=user,password=password,database=database,charset=charset)
    print('数据库已连接')
    return db

def reaf_data(sql): #查询信息
    db=get_conn()
    cursor=db.cursor()
    data=None
    try:
        cursor.execute(sql)
        data=cursor.fetchall()
        print('数据已读取')
    except Exception as e:
        print(e)
    cursor.close()
    db.close()
    return data

def write_data(sql): #插入信息
    db = get_conn()
    cursor=db.cursor()
    rows=0
    try:
        rows=cursor.execute(sql)
        db.commit()
        print('数据已插入')
    except Exception as e:
        db.rollback()
        print(e)

    cursor.close()
    db.close()
    return rows
def mod_data(sql):
    db = get_conn()
    cursor = db.cursor()
    mod=0
    try:
        mod=cursor.execute(sql)
        db.commit()
        print('数据已修改')
    except Exception as e:
        db.rollback()
        print(e)
    cursor.close()
    db.close()
    return mod

def del_data(sql):
    db=get_conn()
    cursor = db.cursor()
    rem=0
    try:
        rem=cursor.execute(sql)
        db.commit()
        print('数据已删除')
    except Exception as e:
        db.rollback()
        print(e)
    cursor.close()
    db.close()
    return rem


# if __name__ =='__main__':
    # sql="select * from products"
    # print(reaf_data(sql))
    # sql = "INSERT INTO `market`.`products`(`id`, `num`, `name`, `price`, `discount`) VALUES (5, 1005, '桔子', 2.00, 1.00);"
    # print(write_data(sql))
    # sql = "update products set price=5 where id=5"
    # print(mod_data(sql))
    # sql='delete from products where id=5'
    # print(del_data(sql))
